package com.glo.spreadsheet2db.services.jdbc.repositories;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.glo.spreadsheet2db.model.Spreadsheet;
import com.glo.spreadsheet2db.services.repositories.SpreadsheetRepository;

@Repository
public class JdbcTemplateSpreadsheetRepository implements SpreadsheetRepository, InitializingBean {

	@Value("${jdbc.sql.spreadsheet.queryById}")
	private String spreadsheetByIdQuery;

	@Value("${jdbc.sql.spreadsheet.insert}")
	private String insertSpreadsheetQuery;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Spreadsheet getSpreadsheetById(long id) {
		return jdbcTemplate.queryForObject(spreadsheetByIdQuery,
				spreadsheetMapper, id);
	}

	public Spreadsheet saveSpreadsheet(Spreadsheet spreadsheet) {

		SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		simpleJdbcInsert.setTableName("spreadsheet");
		simpleJdbcInsert.setColumnNames(Arrays.asList("savedDate", "rowCount", "colCount"));
		simpleJdbcInsert.setGeneratedKeyName("id");

		Map<String, Object> args = new HashMap<String, Object>();
		args.put("savedDate", spreadsheet.getSavedDate().getTime() );
		args.put("rowCount", spreadsheet.getRowCount());
		args.put("colCount", spreadsheet.getColCount());

		Number id = simpleJdbcInsert.executeAndReturnKey(args);
		spreadsheet.setId(id.longValue());
		return spreadsheet;
	}

	public void afterPropertiesSet() throws Exception {
		Assert.notNull(this.jdbcTemplate, "the jdbcTemplate can't be null!");
		Assert.notNull(this.spreadsheetByIdQuery,
				"the spreadsheetByIdQuery can't be null");
		Assert.notNull(this.insertSpreadsheetQuery,
				"the insertSpreadsheetQuery can't be null");
	}

	private RowMapper<Spreadsheet> spreadsheetMapper = new RowMapper<Spreadsheet>() {

		public Spreadsheet mapRow(ResultSet resultSet, int i)
				throws SQLException {
			long id = resultSet.getLong("id");
			Date savedDate = new Date( resultSet.getLong("savedDate") );			
			int rowCount = resultSet.getInt("rowCount");			
			int colCount = resultSet.getInt("colCount");			
			return new Spreadsheet(id, savedDate, rowCount, colCount);
		}
	};
}
